/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package aptech.semester.code.data;

import aptech.semester.code.conn.MyLib;
import aptech.semester.code.entity.AccountEntity;
import aptech.semester.code.entity.SalaryFormEntity;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author vi
 */
public class AccountList {
    public AccountEntity getAccountLogin(String userName, String password) {
        AccountEntity account = new AccountEntity();
      
        try {
            //1.tao 1 ket noi
            Connection cn = MyLib.getCN();
            if(cn!=null) {
            String sql = "SELECT * FROM ACCOUNT " +
                        "WHERE USERNAME = ? " +
                        "AND PASSWORD = ?";
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setString(1,userName);
            pst.setString(2,password);
            //3.cho thi hanh lenh truy van tren doi tuong nay va ket qua se dc tra ve doi tuong resulltset
            ResultSet rs = pst.executeQuery();
            
            //4. duyuet ket qua tra ve trong resultset rs va fill vao arraylist ds.
            while(rs.next()) {
                account.setAccountId(rs.getInt("ID"));
                account.setEmployeeId(rs.getString("EMPLOYEE_ID"));
                account.setUserName(rs.getString("USERNAME"));
                account.setPassword(rs.getString("PASSWORD"));
                account.setRole(rs.getInt("ROLE"));
            }
            rs.close();
            cn.close();
            }
        } catch (Exception e) {
            
        }
        
        return account;
    }
    
    public AccountEntity getAccountByEmployeeId(String employeeId) {
        AccountEntity account = new AccountEntity();
      
        try {
            //1.tao 1 ket noi
            Connection cn = MyLib.getCN();
            if(cn!=null) {
            String sql = "SELECT * FROM ACCOUNT " +
                        "WHERE EMPLOYEE_ID = ? ";
                        
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setString(1,employeeId);
            
            //3.cho thi hanh lenh truy van tren doi tuong nay va ket qua se dc tra ve doi tuong resulltset
            ResultSet rs = pst.executeQuery();
            
            //4. duyuet ket qua tra ve trong resultset rs va fill vao arraylist ds.
            while(rs.next()) {
                account.setAccountId(rs.getInt("ID"));
                account.setEmployeeId(rs.getString("EMPLOYEE_ID"));
                account.setUserName(rs.getString("USERNAME"));
                account.setPassword(rs.getString("PASSWORD"));
                account.setRole(rs.getInt("ROLE"));
            }
            rs.close();
            cn.close();
            }
        } catch (Exception e) {
            
        }
        
        return account;
    }
    
    public boolean insert(AccountEntity account) {
        
        try {
            //1. tao ket noi
            Connection cn = MyLib.getCN();
            
            //2.tao doi tuong lenh insert sql
            String sql = "insert ACCOUNT values(?,?,?,?)";
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setString(1, account.getUserName());
            pst.setString(2, account.getPassword());
            pst.setString(3, account.getEmployeeId());
            pst.setInt(4, account.getRole());
            //3.cho thi hanh cau lenh sql
            int result = pst.executeUpdate();
            pst.close();
            cn.close();
            return result > 0;
 
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        
    }
    
    public boolean update(AccountEntity account) {
        
        try {
            //1. tao ket noi
            Connection cn = MyLib.getCN();
            
            //2.tao doi tuong lenh insert sql
            String sql = "UPDATE ACCOUNT SET USERNAME = ?, PASSWORD = ?, ROLE = ? WHERE ACCOUNT.ID = ?";
            PreparedStatement pst = cn.prepareStatement(sql);
            pst.setString(1, account.getUserName());
            pst.setString(2, account.getPassword());      
            pst.setInt(3, account.getRole());
            pst.setInt(4, account.getAccountId());
            //3.cho thi hanh cau lenh sql
            int result = pst.executeUpdate();
            pst.close();
            cn.close();
            return result > 0;
 
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        
    }
    
    public boolean delete(int accountId) {
        
        try {
            //1. tao ket noi
            Connection cn = MyLib.getCN();
            
            //2.tao doi tuong lenh insert sql
            String sql = "DELETE from ACCOUNT where ID = ?";
            PreparedStatement pst = cn.prepareStatement(sql);
           
            pst.setInt(1, accountId);
            
            //3.cho thi hanh cau lenh sql
            int r = pst.executeUpdate();
            
            cn.close();
            return (r > 0);
            
            
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
      
    }
}
